const mysql = require('mysql')
const db = mysql.createPool({
  host: '',
  user: '',
  password: '',
  database: '',
  port: 3306,
  multipleStatements: true
})

//测试数据库联通性
db.query('select 1', (err, results) => {
  if (err) {
    console.log(err)
  } else {
    console.log(results)
  }
})

// 插入数据普通方式
const sqlStr1 = 'insert into user (username,password) values (?,?)'
db.query(sqlStr1, ['zs', '111111'], (err, results) => {
  if (err) return console.log(err.message)
  if (results.affectedRows == 1) {
    console.log('插入数据成功')
  }
})

// 插入数据快捷方式
const sqlStr2 = 'insert into user ?'
const user = { username: 'zs', password: '111111' }
db.query(sqlStr2, user, (err, results) => {
  if (err) return console.log(err.message)
  if (results.affectedRows == 1) {
    console.log('插入数据成功')
  }
})

// 更新数据普通方式
const user3 = { id: 1, username: 'zs', password: '111111' }
const sqlStr3 = 'update user set username=?,password=? where id=?'
db.query(sqlStr3, [user3.username, user3.password, user3.id], (err, results) => {
  if (err) return console.log(err.message)
  if (results.affectedRows == 1) {
    console.log('更新数据成功')
  }
})

// 更新数据快捷方式
const sqlStr4 = 'update user set ? where id=?'
db.query(sqlStr4, [user3, user3.id], (err, results) => {
  if (err) return console.log(err.message)
  if (results.affectedRows == 1) {
    console.log('更新数据成功')
  }
})

// 删除数据
const sqlStr5 = 'delete from user where id=?'
db.query(sqlStr5, 1, (err, results) => {
  if (err) return console.log(err.message)
  if (results.affectedRows == 1) {
    console.log('删除数据成功')
  }
})
